﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;

namespace simplify.db
{
    /// <summary>
    /// Summary description for DBAgent
    /// </summary>
    public static class DBAgent
    {
        private const string RETURN_VALUE_KEY = "return_value";

        /// <summary>
        /// Convenience method for calling a stored procedure that returns data
        /// </summary>
        /// <param name="dataSource">The name of the connection string to use from the configuration file</param>
        /// <param name="procedureName">The name of the stored procedure to call</param>
        /// <param name="dataSet">A DataSet to fill with data</param>
        /// <param name="parameters">SqlParameters to pass to the stored procedure</param>
        /// <returns>The return code given by the stored procedure</returns>
        public static int CallQueryProc(string dataSource, string procedureName, ref DataSet dataSet, SqlParameter[] parameters = null)
        {
            //Open a SQL connection
            SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings[dataSource].ConnectionString);
            try
            {
                //Initialize a SQL command
                SqlCommand cmd = new SqlCommand(procedureName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //Set parameters if given any
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                //Also add a parameter for the return value
                cmd.Parameters.Add(new SqlParameter() { DbType = DbType.Int32, Direction = ParameterDirection.ReturnValue, ParameterName = RETURN_VALUE_KEY });

                //Initialize the given data set
                if (dataSet == null)
                {
                    dataSet = new DataSet();
                }
                //Get data from the database
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                dataAdapter.Fill(dataSet);

                //Return the return code
                return (int)cmd.Parameters[RETURN_VALUE_KEY].Value;
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                //Dispose of the connection no matter what
                conn.Close();
            }
        }

        /// <summary>
        /// Convenience method for calling a procedure that does not return data
        /// </summary>
        /// <param name="dataSource">The name of the connection string to use from the configuration file</param>
        /// <param name="procedureName">The name of the stored procedure to call</param>
        /// <param name="parameters">SqlParameters to pass to the stored procedure</param>
        /// <returns>The return code given by the stored procedure</returns>
        public static int CallNonQueryProc(string dataSource, string procedureName, SqlParameter[] parameters = null)
        {
            //Initialize a SQL connection
            SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings[dataSource].ConnectionString);
            try
            {
                //Initialize a SQL command
                SqlCommand cmd = new SqlCommand(procedureName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //Load parameters if given any
                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                //Also load the return value parameter
                cmd.Parameters.Add(new SqlParameter(){ ParameterName = RETURN_VALUE_KEY, Direction = ParameterDirection.ReturnValue, DbType = DbType.Int32});

                conn.Open();

                //Execute the query
                cmd.ExecuteNonQuery();

                //Return the return code from the procedure
                return (int)cmd.Parameters[RETURN_VALUE_KEY].Value;
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                //Dispose of the connection no matter what
                conn.Close();
            }
        }
    }
}